﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using University.Workshop.Model;
using iTextSharp;
using iTextSharp.text;
using iTextSharp.text.pdf;

namespace University.Workshop.DataAccess
{
    public class DataAccessKardex
    {

        public static ICollection<Model.Kardex> SearchKardexByParameterAll(Model.Kardex kardexFind)
        {
            WFPEntities.Instance.DiscardChanges();
            try
            {
                var q = from k in WFPEntities.Instance.Kardex
                        where (k.Person.Names.Contains(kardexFind.Person.Names) &&
                               k.Person.LastNames.Contains(kardexFind.Person.LastNames) &&
                               k.Person.PersonId.Contains(kardexFind.Person.PersonId))
                        select k;
                return q.ToList();
            }
            catch
            {
                WFPEntities.Instance.DiscardChanges();
                var q = from k in WFPEntities.Instance.Kardex
                        where (k.Person.Names.Contains(kardexFind.Person.Names) &&
                               k.Person.LastNames.Contains(kardexFind.Person.LastNames) &&
                               k.Person.PersonId.Contains(kardexFind.Person.PersonId))
                        select k;
                return q.ToList();
            }

        }

        public static void CreateKardex(Model.Kardex newKardex)
        {
            try
            {
                WFPEntities.Instance.Kardex.Add(newKardex);
                WFPEntities.Instance.SaveChanges();
            }
            catch
            {
                WFPEntities.Instance.DiscardChanges();
                WFPEntities.Instance.Kardex.Add(newKardex);
                WFPEntities.Instance.SaveChanges();
            }
        }

        public static List<Model.Kardex> SearchKardexesByKardexId(Model.Kardex kardex)
        {
            WFPEntities.Instance.DiscardChanges();
            try
            {
                return (from k in WFPEntities.Instance.Kardex
                        where k.KardexId == kardex.KardexId
                        select k).ToList();
            }
            catch
            {
                WFPEntities.Instance.DiscardChanges();
                return (from k in WFPEntities.Instance.Kardex
                        where k.KardexId == kardex.KardexId
                        select k).ToList();
            }
        }

        public static List<Model.Kardex> SearchKardexesByClientId(Model.Kardex kardex)
        {
            WFPEntities.Instance.DiscardChanges();

            return (from k in WFPEntities.Instance.Kardex
                    where k.ClientId == kardex.ClientId
                    select k).ToList();
        }

        public static ICollection<Model.Kardex> SearchKardexByParameterEnable(Model.KardexHelper kardex)
        {
            WFPEntities.Instance.DiscardChanges();

            if (kardex.Allotment != string.Empty && kardex.Square != string.Empty)
            {
                var p = from k in WFPEntities.Instance.Kardex
                        where (k.Person.Names.StartsWith(kardex.Name) &&
                               k.Person.LastNames.StartsWith(kardex.LastName) &&
                               k.Allotment.FirstOrDefault().AllotmentName.Equals(kardex.Allotment)) &&
                               k.Allotment.FirstOrDefault().SquareId.Equals(kardex.Square) && (k.Enable)
                        select k;
                return p.ToList();
            }
            else
            {
                var q = from k in WFPEntities.Instance.Kardex
                        where (k.Person.Names.StartsWith(kardex.Name) &&
                               k.Person.LastNames.StartsWith(kardex.LastName) &&
                               k.Allotment.FirstOrDefault().AllotmentName.StartsWith(kardex.Allotment)) &&
                               k.Allotment.FirstOrDefault().SquareId.StartsWith(kardex.Square) && (k.Enable)
                        select k;
                return q.ToList();
                
            }
            

        }

        public static ICollection<Model.Kardex> SearchKardexByParameterEnable(Model.Kardex kardexFind)
        {
            WFPEntities.Instance.DiscardChanges();
            try
            {
                var q = from k in WFPEntities.Instance.Kardex
                        where (k.Person.Names.Contains(kardexFind.Person.Names) &&
                               k.Person.LastNames.Contains(kardexFind.Person.LastNames) &&
                               k.Person.PersonId.Contains(kardexFind.Person.PersonId)) && (k.Enable)
                        select k;
                return q.ToList();
            }
            catch
            {
                WFPEntities.Instance.DiscardChanges();
                var q = from k in WFPEntities.Instance.Kardex
                        where (k.Person.Names.Contains(kardexFind.Person.Names) &&
                               k.Person.LastNames.Contains(kardexFind.Person.LastNames) &&
                               k.Person.PersonId.Contains(kardexFind.Person.PersonId)) && (k.Enable)
                        select k;
                return q.ToList();
            }
        }

        public static ICollection<Model.Kardex> SearchKardexByParameterDisable(Model.Kardex kardexFind)
        {
            WFPEntities.Instance.DiscardChanges();
            try
            {
                var q = from k in WFPEntities.Instance.Kardex
                        where (k.Person.Names.Contains(kardexFind.Person.Names) &&
                               k.Person.LastNames.Contains(kardexFind.Person.LastNames) &&
                               k.Person.PersonId.Contains(kardexFind.Person.PersonId)) && (k.Enable == false)
                        select k;
                return q.ToList();
            }
            catch
            {
                WFPEntities.Instance.DiscardChanges();
                var q = from k in WFPEntities.Instance.Kardex
                        where (k.Person.Names.Contains(kardexFind.Person.Names) &&
                               k.Person.LastNames.Contains(kardexFind.Person.LastNames) &&
                               k.Person.PersonId.Contains(kardexFind.Person.PersonId)) && (k.Enable == false)
                        select k;
                return q.ToList();
            }
        }

        public static void UpdateKardex(Model.Kardex modifyKardex)
        {
            try
            {
                Model.Kardex q = (from k in WFPEntities.Instance.Kardex
                                  where k.KardexId == modifyKardex.KardexId
                                  select k).First();
                q.Enable = true;
                WFPEntities.Instance.SaveChanges();
            }
            catch
            {
                WFPEntities.Instance.DiscardChanges();
                Model.Kardex q = (from k in WFPEntities.Instance.Kardex
                                  where k.KardexId == modifyKardex.KardexId
                                  select k).First();
                q.Enable = true;
                WFPEntities.Instance.SaveChanges();
            }
        }

        public static void DisableKardex(Model.Kardex KardexForDisable)
        {
            try
            {
                Model.Kardex q = (from k in WFPEntities.Instance.Kardex
                                  where k.KardexId == KardexForDisable.KardexId
                                  select k).First();
                q.Enable = false;
                WFPEntities.Instance.SaveChanges();
            }
            catch
            {
                WFPEntities.Instance.DiscardChanges();
                Model.Kardex q = (from k in WFPEntities.Instance.Kardex
                                  where k.KardexId == KardexForDisable.KardexId
                                  select k).First();
                q.Enable = false;
                WFPEntities.Instance.SaveChanges();
            }
        }
        #region Option 1
        public static void CreateReportKardexAndOpen(Model.Kardex kardex, int year)
        {
            kardex = SearchKardexesByKardexId(kardex).First();
            string fileName = System.IO.Path.GetTempPath() + Guid.NewGuid().ToString() + ".pdf";
            Document document = new Document(new Rectangle(PageSize.LETTER.Height, PageSize.LETTER.Width), 0, 0, 80,
                                             30);
            PdfWriter.GetInstance(document, new FileStream(fileName, FileMode.Create));
            document.Open();
            Paragraph parrafo = new Paragraph();

            parrafo.Alignment = Element.ALIGN_CENTER;
            parrafo.Font = FontFactory.GetFont("Arial", 24);
            parrafo.Font.SetStyle(Font.BOLD);
            parrafo.Font.SetStyle(Font.UNDERLINE);
            parrafo.Add("Kardex De Socio");
            document.Add(parrafo);
            var p = new Paragraph {Alignment = Element.ALIGN_CENTER, Font = FontFactory.GetFont("Arial", 24) };
            p.Add(@"

");
            document.Add(p);

        PdfPTable unaTabla = new PdfPTable(10);
            unaTabla.SetWidths(new float[] { 47, 36, 35, 40, 39, 
                40, 46,  64, 
                32, 39 });

            //Headers
            unaTabla.AddCell(new PdfPCell(new Phrase(string.Format("Nombre Socio: {0} {1}", kardex.Person.LastNames, kardex.Person.Names)) { }) { Colspan = 6, FixedHeight = 27 });
            unaTabla.AddCell(new PdfPCell(new Phrase(string.Format("Manzano: {0}", kardex.AllotmentModel.SquareId))) { Colspan = 2  , FixedHeight = 27 });
            unaTabla.AddCell(new PdfPCell(new Phrase(string.Format("Lote: {0}", kardex.AllotmentModel.AllotmentName))) { Colspan = 2, FixedHeight = 27 });


            AddSecondHeader(unaTabla,33);

            SetThirdHeardes(year, unaTabla,37);
            SetBodyReport(19, unaTabla, kardex,year);
            document.Add(unaTabla);
            document.Close();
            Process prc = new System.Diagnostics.Process();
            prc.StartInfo.FileName = fileName;
            prc.Start();
        }

        private static void SetBodyReport(int heightHeader, PdfPTable unaTabla, Model.Kardex kardex, int year)
        {
            var finesForYear = GetFineControlforKardexInYear(kardex, year);
            var WaterReadigControlForYear = GetWaterReadingControlForYearInYear(kardex, year);
            List<string> months = new List<string>
                                      {
                                          "Enero",
                                          "Febrero",
                                          "Marzo",
                                          "Abril",
                                          "Mayo",
                                          "Junio",
                                          "Julio",
                                          "Agosto",
                                          "Septiembre",
                                          "Octubre",
                                          "Noviembre",
                                          "Diciembre"
                                      };
            for (int i = 0; i < 12; i++)
            {
                DateTime? datePayment=null;
                decimal Total = 0;
                AddNormalCell(unaTabla, months.ElementAt(i), heightHeader);
                int CurrentMonth = i + 1;
               
                if (WaterReadigControlForYear[i+1].CurrentMeasurerRead!=0)
                {
                    AddNormalCell(unaTabla, WaterReadigControlForYear[i ].CurrentMeasurerRead.ToString(), heightHeader);
                    AddNormalCell(unaTabla, WaterReadigControlForYear[i + 1].CurrentMeasurerRead.ToString(), heightHeader);
                    AddNormalCell(unaTabla, ((WaterReadigControlForYear[i + 1].CurrentMeasurerRead - WaterReadigControlForYear[i ].CurrentMeasurerRead)).ToString(),
                                  heightHeader);
                    AddNormalCell(unaTabla, WaterReadigControlForYear[i + 1].ReadingDate.ToShortDateString(), heightHeader);
                    
                    var p = GetCollectControlFromMonthYearAndKardex(CurrentMonth, year, kardex,1);
                    if (p != null)
                    {
                        Total += p.PaymentAmount;
                        AddNormalCell(unaTabla,
                                      (p.PaymentAmount).ToString(),
                                      heightHeader);
                    datePayment = p.CollectPayDate;
                    }
                    else
                        AddNormalCell(unaTabla,
                                      (0).ToString(),
                                      heightHeader);
                }
                else
                {
                    AddNormalCell(unaTabla, "-----", heightHeader);
                    AddNormalCell(unaTabla, "-----", heightHeader);
                    AddNormalCell(unaTabla, "0", heightHeader);
                    AddNormalCell(unaTabla, "-----", heightHeader);
                    AddNormalCell(unaTabla, "0", heightHeader);
                }
                if (kardex.WaterReadingControls.Count != 0)
                {
                    //var q = kardex.WaterReadingControls.First();
                    //AddNormalCell(unaTabla,
                    //              (((q.SystemDate.Date.Month == i) && (q.SystemDate.Date.Year == year)) ? "Si" : "No"),
                    //              heightHeader);
                }
                else
                {
                    //AddNormalCell(unaTabla,
                    //           "No",
                    //           heightHeader);
                }
                //AddNormalCell(unaTabla,
                //              kardex.Reconnections.Count(
                //                  x =>
                //                  ((x.Eneabled) && (x.SystemDate.Date.Year == year) &&
                //                   (x.SystemDate.Date.Month == i + 1))) == 1
                //                  ? "Si"
                //                  : "No", heightHeader); //Reconexiónes
                decimal multa = 0;
                if(finesForYear!=null)
                finesForYear.Where(x => ((x.ControlDate.Date.Month == i + 1) && (x.ControlDate.Date.Year == year))).
                    ToList().ForEach(x => multa += x.PaymentAmount);
                if(multa>0)
                    finesForYear.ForEach(x =>
                                             {
                                                 DateTime? dateTime = datePayment >= x.CollectPayDate ? datePayment : x.CollectPayDate;
                                                 datePayment=dateTime;
                                             });

                //GetFineControlforKardexInYear(kardex, year).Where(x => ((x.CollectPayDate.Value.Date.Month == i + 1) && (x.CollectPayDate.Value.Date.Year == year))).
                //    ToList().ForEach(x => multa += x.CollectPaymentAmount);
                Total += multa;
                AddNormalCell(unaTabla,multa.ToString() , heightHeader);//Multa
                //AddNormalCell(unaTabla, months.ElementAt(i), heightHeader);//Mantenimiento
                var m = GetOthersControlFromMonthYearAndKardex(CurrentMonth, year, kardex, 1);
                if (m != null)
                {
                    //otros
                    decimal subtotal=0;
                    m.ForEach(x => { subtotal += x.PaymentAmount;
                    DateTime? dateTime = datePayment >= x.CollectPayDate ? datePayment : x.CollectPayDate;
                    datePayment = dateTime;
                    });
                    AddNormalCell(unaTabla,
                                  (subtotal).ToString(),
                                  heightHeader);
                    Total += subtotal;
                }
                else
                    AddNormalCell(unaTabla,
                                  (0).ToString(),
                                  heightHeader);
                AddNormalCell(unaTabla, Total.ToString(), heightHeader);//Total Bs
                if(datePayment!=null)
                AddNormalCell(unaTabla, datePayment.Value.ToShortDateString(), heightHeader);//Fecha
                else
                    AddNormalCell(unaTabla, "---", heightHeader);//Fecha
            }
        }

        private static List<Model.WaterReadingControl> GetWaterReadingControlForYearInYear(Model.Kardex kardex, int year)
        {

            DateTime dateinit = new DateTime(year-1, 12, 01);
            DateTime dateend = (new DateTime(year+1, 01, 01)).AddMilliseconds(-1);
            var q = (from p in WFPEntities.Instance.WaterReadingControl
                     where p.KardexId == kardex.KardexId &&
                            p.ReadingDate >= dateinit && p.ReadingDate <= dateend
                            orderby p.ReadingDate ascending 
                     select p).ToList();
            for (int i = q.Count; i < 13; i++)
            {
                q.Insert(0,new WaterReadingControl());
            }
            return q;
        }

        private static List<Model.FineCollectControl> GetFineControlforKardexInYear(Model.Kardex kardex, int year)
        {


            DateTime dateinit = new DateTime(year, 01, 01);
            DateTime dateend = dateinit.AddYears(1).AddMilliseconds(-1);
            var q = (from p in WFPEntities.Instance.FineCollectControl
                     where p.KardexId == kardex.KardexId &&
                            p.ControlDate >= dateinit && p.ControlDate <= dateend &&
                           p.IsPaid
                     select p).ToList();
            if (q.Count != 0)
                return q;
            else
                return null;
        }

        private static List<Model.OtherCollectControl> GetOthersControlFromMonthYearAndKardex(int CurrentMonth, int year, Model.Kardex kardex, int typeSelected)
        {

            DateTime dateinit = new DateTime(year, CurrentMonth, 01);
            DateTime dateend = dateinit.AddMonths(1).AddMilliseconds(-1);
            var q = (from p in WFPEntities.Instance.OtherCollectControls
                     where p.KardexId == kardex.KardexId &&
                           //p.ControlDate >= dateinit && p.ControlDate <= dateend &&
                           p.CollectType == typeSelected //&&
                           //p.OtherCollectControlIsPaid
                     select p).ToList();
            if (q.Count != 0)
                return q;
            else
                return null;
        }

        private static Model.WaterCollectControl GetCollectControlFromMonthYearAndKardex(int CurrentMonth, int year, Model.Kardex kardex, int typeSelected)
        {
            DateTime dateinit = new DateTime(year,CurrentMonth,01);
            DateTime dateend = dateinit.AddMonths(1).AddMilliseconds(-1);
            var q= (from p in WFPEntities.Instance.WaterCollectControl
                    where p.KardexId == kardex.KardexId &&
                          p.ReadingDate >= dateinit && p.ReadingDate <= dateend &&
                          p.CollectType==typeSelected &&
                          p.IsPaid
                    select p).ToList() ;
            if (q.Count != 0)
                return q.First();
            else
                return null;
        }

        private static void AddSecondHeader(PdfPTable unaTabla,int heightHeader)
        {
            unaTabla.AddCell(new PdfPCell(new Phrase("REGISTRO DE LECTURA MEDIDOR")) { Colspan = 5, HorizontalAlignment = 1, FixedHeight = heightHeader });
            unaTabla.AddCell(new PdfPCell(new Phrase("DETALLE EN BS")) { Colspan = 3, HorizontalAlignment = 1, FixedHeight = heightHeader });
            unaTabla.AddCell(new PdfPCell(new Phrase("CONTROL DE PAGO")) { Colspan = 2, HorizontalAlignment = 1, FixedHeight = heightHeader });
        }

        private static void SetThirdHeardes(int year, PdfPTable unaTabla, int heightHeader)
        {
            AddNormalCell(unaTabla, string.Format("Año {0} Meses", year), heightHeader);
            AddNormalCell(unaTabla, "Lectura Anterior M3", heightHeader);
            AddNormalCell(unaTabla, "Lectura Actual M3", heightHeader);
            AddNormalCell(unaTabla, "Consumo M3", heightHeader);
            AddNormalCell(unaTabla, "Fecha Lectura", heightHeader);
            AddNormalCell(unaTabla, "Costo Consumo", heightHeader);
            //AddNormalCell(unaTabla, "Conexión Nueva", heightHeader);
            //AddNormalCell(unaTabla, "Reconexión", heightHeader);
            AddNormalCell(unaTabla, "Multa", heightHeader);
            //AddNormalCell(unaTabla, "Mantenimiento", heightHeader);
            AddNormalCell(unaTabla, "Otros", heightHeader);
            AddNormalCell(unaTabla, "Total Bs", heightHeader);
            AddNormalCell(unaTabla, "Fecha",heightHeader);
        }

        private static void AddNormalCell(PdfPTable unaTabla, string content, int heightCell)
        {
            Phrase p = new Phrase(content) ;
            p.Font.Size = 10;
            unaTabla.AddCell(new PdfPCell(p) { HorizontalAlignment = 1, FixedHeight = heightCell});
        }
        #endregion
        #region Option 2
        
        public static void CreateKardexAndOpen1(Model.Kardex kardex, int year)
        {
            kardex = SearchKardexesByKardexId(kardex).First();
            string fileName = System.IO.Path.GetTempPath() + Guid.NewGuid().ToString() + ".pdf";
            Document document = new Document(new Rectangle(PageSize.LETTER.Height, PageSize.LETTER.Width), 0, 0, 80,
                                             30);
            PdfWriter.GetInstance(document, new FileStream(fileName, FileMode.Create));
            document.Open();
            Paragraph parrafo = new Paragraph();

            parrafo.Alignment = Element.ALIGN_CENTER;
            parrafo.Font = FontFactory.GetFont("Arial", 24);
            parrafo.Font.SetStyle(Font.BOLD);
            parrafo.Font.SetStyle(Font.UNDERLINE);

            parrafo.Add("Kardex De Socio");
            document.Add(parrafo);
            var p = new Paragraph {Alignment = Element.ALIGN_CENTER, Font = FontFactory.GetFont("Arial", 24) };
            p.Add(@"

");
            document.Add(p);

        PdfPTable unaTabla = new PdfPTable(13);
            unaTabla.SetWidths(new float[] { 47, 36, 35, 40, 34, 
                40, 40, 46, 30, 64, 
                32, 33, 30 });

            //Headers
            unaTabla.AddCell(new PdfPCell(new Phrase(string.Format("Nombre Socio: {0} {1}", kardex.Person.LastNames, kardex.Person.Names)) { }) { Colspan = 7, FixedHeight = 27 });
            unaTabla.AddCell(new PdfPCell(new Phrase(string.Format("Manzano: {0}", kardex.AllotmentModel.SquareId))) { Colspan = 3  , FixedHeight = 27 });
            unaTabla.AddCell(new PdfPCell(new Phrase(string.Format("Lote: {0}", kardex.AllotmentModel.AllotmentName))) { Colspan = 3, FixedHeight = 27 });


            AddSecondHeader1(unaTabla,33);

            SetThirdHeardes1(year, unaTabla,37);
            SetBodyReport1(19, unaTabla, kardex,year);
            document.Add(unaTabla);
            document.Close();
            Process prc = new System.Diagnostics.Process();
            prc.StartInfo.FileName = fileName;
            prc.Start();
        }

        private static void SetBodyReport1(int heightHeader, PdfPTable unaTabla, Model.Kardex kardex, int year)
        {
            List<string> months = new List<string>
                                      {
                                          "Enero",
                                          "Febrero",
                                          "Marzo",
                                          "Abril",
                                          "Mayo",
                                          "Junio",
                                          "Julio",
                                          "Agosto",
                                          "Septiembre",
                                          "Octubre",
                                          "Noviembre",
                                          "Diciembre"
                                      };
            for (int i = 0; i < 12; i++)
            {
                decimal subtotal = 0;
                AddNormalCell1(unaTabla, months.ElementAt(i), heightHeader);
                ICollection<Model.WaterReadingControl> ws =
                    kardex.WaterReadingControls.Where(
                        x =>
                        (((x.ReadingDate.Date.Year == (i == 1 ? (year - 1) : year)) &&
                          (x.ReadingDate.Date.Month == (i == 1 ? 12 : (i - 1)))) ||
                         (x.ReadingDate.Date.Year == year) && (x.ReadingDate.Date.Month == i))).ToList();
             
                var fees=DataAccessFeesAndFines.GetFeesVigentInDate(ws.ElementAt(1).ReadingDate)??new FeesAndFines(){BaseRate = 0,Charge = 0,MeterViolation = 0,EffectiveDate = DateTime.Now, MonthlyContribution = 0,NewMember = 0,NewConection = 0, NonPayment = 0,Reconnection = 0}
                ;
                if (ws.Count == 2)
                {
                    AddNormalCell1(unaTabla, ws.First().CurrentMeasurerRead.ToString(), heightHeader);
                    AddNormalCell1(unaTabla, ws.ElementAt(1).CurrentMeasurerRead.ToString(), heightHeader);
                    AddNormalCell1(unaTabla, ((ws.ElementAt(1).CurrentMeasurerRead - ws.ElementAt(0).CurrentMeasurerRead)).ToString(),
                                  heightHeader);
                    AddNormalCell1(unaTabla, ws.ElementAt(1).ReadingDate.ToShortDateString(), heightHeader);

                    decimal cost = (decimal) (ws.ElementAt(1).CurrentMeasurerRead - ws.ElementAt(0).CurrentMeasurerRead)*fees.BaseRate;
                    AddNormalCell1(unaTabla,
                                  cost.ToString(),
                                  heightHeader);
                    subtotal += cost;
                }
                else
                {
                    AddNormalCell1(unaTabla, "-----", heightHeader);
                    AddNormalCell1(unaTabla, "-----", heightHeader);
                    AddNormalCell1(unaTabla, "0", heightHeader);
                    AddNormalCell1(unaTabla, "-----", heightHeader);
                    AddNormalCell1(unaTabla, "0", heightHeader);

                }
                if (kardex.WaterReadingControls.Count != 0)
                {
                    var q = kardex.WaterReadingControls.First();
                    var newconection = ((q.ReadingDate.Date.Month == i) && (q.ReadingDate.Date.Year == year))
                                           ? fees.NewConection
                                           : 0;

                    AddNormalCell1(unaTabla
                                  ,newconection.ToString(),
                                  heightHeader);
                    subtotal += newconection;
                }
                else
                {
                    AddNormalCell1(unaTabla,
                               "----",
                               heightHeader);
                }
                var recon =
                    kardex.Reconnections.Count(
                        x =>
                        ((x.State == 1) && (x.SystemDate.Date.Year == year) &&
                         (x.SystemDate.Date.Month == i + 1))) == 1
                        ? fees.Reconnection: 0;
                
                AddNormalCell1(unaTabla,recon.ToString(), heightHeader); //Reconexiónes
                subtotal += recon;
                decimal multa = 0;
                kardex.CollectsControls.Where(x => ((x.CollectPayDate.Value.Date.Month == i + 1) && (x.CollectPayDate.Value.Date.Year == year) && x.IsPaid&&(x is FineCollectControl))).
                    ToList().ForEach(x => multa += x.PaymentAmount);
                subtotal += multa;
                decimal mantenimiento = 0;
                kardex.CollectsControls.Where(x => ((x.CollectPayDate.Value.Date.Month == i + 1) && (x.CollectPayDate.Value.Date.Year == year) && x.IsPaid && (x is WaterCollectControl))).
                    ToList().ForEach(x => multa += x.PaymentAmount);
                subtotal += mantenimiento;
                decimal otros = 0;
                kardex.CollectsControls.Where(x => ((x.CollectPayDate.Value.Date.Month == i + 1) && (x.CollectPayDate.Value.Date.Year == year) && x.IsPaid && (x is OtherCollectControl))).
                    ToList().ForEach(x => multa += x.PaymentAmount);
                subtotal += otros;
                AddNormalCell1(unaTabla,fees.NonPayment.ToString() , heightHeader);//Multa
                AddNormalCell1(unaTabla, mantenimiento.ToString(), heightHeader);//Mantenimiento
                AddNormalCell1(unaTabla, otros.ToString(), heightHeader);// Otros
                AddNormalCell1(unaTabla, subtotal.ToString(), heightHeader);//Total Bs
                AddNormalCell1(unaTabla, "-----", heightHeader);//Fecha
            }
        }

        private static void AddSecondHeader1(PdfPTable unaTabla,int heightHeader)
        {
            unaTabla.AddCell(new PdfPCell(new Phrase("REGISTRO DE LECTURA MEDIDOR")) { Colspan = 5, HorizontalAlignment = 1, FixedHeight = heightHeader });
            unaTabla.AddCell(new PdfPCell(new Phrase("DETALLE EN BS")) { Colspan = 6, HorizontalAlignment = 1, FixedHeight = heightHeader });
            unaTabla.AddCell(new PdfPCell(new Phrase("CONTROL DE PAGO")) { Colspan = 2, HorizontalAlignment = 1, FixedHeight = heightHeader });
        }

        private static void SetThirdHeardes1(int year, PdfPTable unaTabla, int heightHeader)
        {
            AddNormalCell1(unaTabla, string.Format("Año {0} Meses", year), heightHeader);
            AddNormalCell1(unaTabla, "Lectura Anterior M3", heightHeader);
            AddNormalCell1(unaTabla, "Lectura Actual M3", heightHeader);
            AddNormalCell1(unaTabla, "Consumo M3", heightHeader);
            AddNormalCell1(unaTabla, "Fecha Lectura", heightHeader);
            AddNormalCell1(unaTabla, "Costo Consumo", heightHeader);
            AddNormalCell1(unaTabla, "Conexión Nueva", heightHeader);
            AddNormalCell1(unaTabla, "Reconexión", heightHeader);
            AddNormalCell1(unaTabla, "Multa", heightHeader);
            AddNormalCell1(unaTabla, "Mantenimiento", heightHeader);
            AddNormalCell1(unaTabla, "Otros", heightHeader);
            AddNormalCell1(unaTabla, "Total Bs", heightHeader);
            AddNormalCell1(unaTabla, "Fecha",heightHeader);
        }

        private static void AddNormalCell1(PdfPTable unaTabla, string content, int heightCell)
        {
            Phrase p = new Phrase(content) ;
            p.Font.Size = 10;
            unaTabla.AddCell(new PdfPCell(p) { HorizontalAlignment = 1, FixedHeight = heightCell});
        }
 
        #endregion

        internal static ICollection<Kardex> GetActiveKardex()
        {
            WFPEntities.Instance.DiscardChanges();
            var q = from k in WFPEntities.Instance.Kardex
                    where (k.Enable == true)
                    select k;
            return q.ToList();
        }

        internal static ICollection<Kardex> GetActiveKardexWithoutMeasurer()
        {
            WFPEntities.Instance.DiscardChanges();
            var q = from k in WFPEntities.Instance.Kardex
                    where (k.WaterMeasurer == string.Empty) && (k.Enable == true)
                    select k;
            return q.ToList();
        }

        internal static ICollection<Kardex> GetActiveKardexWithMeasurer()
        {
            WFPEntities.Instance.DiscardChanges();
            var q = from k in WFPEntities.Instance.Kardex
                    where (k.Enable == true) && k.HasAWaterMeasurer
                    select k;
            return q.ToList();
        }

        public static void UpdateKardexMeasure(Model.Kardex modifyKardex)
        {
            WFPEntities.Instance.DiscardChanges();
            Model.Kardex q = (from k in WFPEntities.Instance.Kardex
                                  where k.KardexId == modifyKardex.KardexId
                                  select k).First();
            q.WaterMeasurer = modifyKardex.WaterMeasurer;
            q.HasAWaterMeasurer = true;

            WFPEntities.Instance.SaveChanges();
        }
    }

}
